package com.example.demo.server.dao;

import com.example.demo.ds.MyList;
import com.example.demo.server.model.ArticleInfo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class ArticleDao {

    public int insert(ArticleInfo articleInfo) {
        Connection connection = null;
        PreparedStatement statement = null;
        int ret = 0;
        try {
            connection = DBUtils.getConnection();
            String sql = "insert into articleinfo (title, content, summary, uid) values(?, ?, ?, ?)";
            statement = connection.prepareStatement(sql);
            // 设置信息
            statement.setString(1, articleInfo.getTitle());
            statement.setString(2, articleInfo.getContent());
            statement.setString(3, articleInfo.getSummary());
            statement.setInt(4, articleInfo.getUid());
            // 存储
            ret = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(connection, statement, null);
        }
        return ret;
    }

    public MyList<ArticleInfo> getListByUid(int uid) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet set = null;
        MyList<ArticleInfo> list = new MyList<>();
        try {
            connection = DBUtils.getConnection();
            String sql = "select * from articleinfo where uid = ? order by id desc";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, uid);
            set = statement.executeQuery();
            while(set.next()) {
                ArticleInfo articleInfo = new ArticleInfo();
                articleInfo.setId(set.getInt("id"));
                articleInfo.setUid(set.getInt("uid"));
                articleInfo.setSummary(set.getString("summary"));
                articleInfo.setContent(set.getString("content"));
                articleInfo.setTitle(set.getString("title"));
                articleInfo.setCreatetime(set.getTimestamp("createtime"));
                articleInfo.setRcount(set.getInt("rcount"));
                articleInfo.setState(set.getInt("state"));
                list.add(articleInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(connection, statement, set);
        }
        return list;
    }

    public int delete(ArticleInfo articleInfo) {
        Connection connection = null;
        PreparedStatement statement = null;
        int ret = 0;
        try {
            connection = DBUtils.getConnection();
            String sql = "delete from articleinfo where id = ? and uid = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, articleInfo.getId());
            statement.setInt(2, articleInfo.getUid());
            ret = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(connection,statement, null);
        }
        return ret;
    }

    public int updateArticle(ArticleInfo articleInfo) {
        Connection connection = null;
        PreparedStatement statement = null;
        int ret = 0;
        try {
            connection = DBUtils.getConnection();
            String sql = "update articleinfo set content = ?, title = ?, summary = ?, updatetime = now() where id = ? and uid = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, articleInfo.getContent());
            statement.setString(2, articleInfo.getTitle());
            statement.setString(3, articleInfo.getSummary());
            statement.setInt(4, articleInfo.getId());
            statement.setInt(5, articleInfo.getUid());
            ret = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(connection,statement, null);
        }
        return ret;
    }

    public int getArtNumberByUid(int uid) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet set = null;
        int count = 0;
        try {
            connection = DBUtils.getConnection();
            String sql = "select count(*) as count from articleinfo where uid = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, uid);
            set = statement.executeQuery();
            if(set.next()) {
                count = set.getInt("count");
            }else {
                return -1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(connection,statement, null);
        }
        return count;
    }

    public int incrementRCount(int aid) {
        Connection connection = null;
        PreparedStatement statement = null;
        int ret = 0;
        try {
            connection = DBUtils.getConnection();
            String sql = "update articleinfo set rcount = rcount + 1 where id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, aid);
            ret = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(connection,statement, null);
        }
        return ret;
    }

    public MyList<ArticleInfo> getSubList(int limit, int offset) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet set = null;
        MyList<ArticleInfo> list = new MyList<>();
        try {
            connection = DBUtils.getConnection();
            String sql = "select * from articleinfo order by id desc limit ? offset ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, limit);
            statement.setInt(2, offset);
            set = statement.executeQuery();
            while(set.next()) {
                ArticleInfo articleInfo = new ArticleInfo();
                articleInfo.setId(set.getInt("id"));
                articleInfo.setUid(set.getInt("uid"));
                articleInfo.setSummary(set.getString("summary"));
                articleInfo.setContent(set.getString("content"));
                articleInfo.setTitle(set.getString("title"));
                articleInfo.setCreatetime(set.getTimestamp("createtime"));
                articleInfo.setRcount(set.getInt("rcount"));
                articleInfo.setState(set.getInt("state"));
                list.add(articleInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(connection, statement, set);
        }
        return list;
    }

    public int getNumber() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet set = null;
        int count = 0;
        try {
            connection = DBUtils.getConnection();
            String sql = "select count(*) as count from articleinfo";
            statement = connection.prepareStatement(sql);
            set = statement.executeQuery();
            if(set.next()) {
                count = set.getInt("count");
            }else {
                return -1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(connection,statement, null);
        }
        return count;
    }

}
